package com.bj58.groupbuy.action.service.impl;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import com.bj58.groupbuy.action.db.CommonDao;
import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.db.IntSystemDao;
import com.bj58.groupbuy.action.model.IRColumns;
import com.bj58.groupbuy.action.model.IRDrillConfig;
import com.bj58.groupbuy.action.model.IRReport;
import com.bj58.groupbuy.action.model.IRSearchConfig;
import com.bj58.groupbuy.action.model.Menu;
import com.bj58.groupbuy.action.model.OperMenu;
import com.bj58.groupbuy.action.model.User;
import com.bj58.groupbuy.action.service.IRColumnsService;
import com.bj58.groupbuy.action.service.IRReportService;
import com.bj58.groupbuy.action.service.IRSearchConfigService;
import com.bj58.groupbuy.action.service.IntReportMainService;
import com.bj58.groupbuy.action.service.MenuService;
import com.bj58.groupbuy.action.service.UserOperService;
import com.bj58.groupbuy.action.util.DateUtil;
import com.bj58.groupbuy.action.util.MathEval;
import com.bj58.groupbuy.action.util.MyStore;
import com.bj58.groupbuy.action.util.SortUtil;
import com.bj58.groupbuy.action.util.StringUtil;
import com.bj58.groupbuy.service.locator.ServiceLocator;
import com.bj58.groupbuy.wf.page.Paginate;
import com.bj58.wf.log.Log;
import com.bj58.wf.log.LogFactory;

public class IntReportMainServiceImpl implements IntReportMainService {
	private IRReportService iRReportService = ServiceLocator.get(IRReportService.class);
	private IRSearchConfigService iRSearchConfigService = ServiceLocator.get(IRSearchConfigService.class);
	private MenuService menuService = ServiceLocator.get(MenuService.class);
	private CommonDao comDao = new CommonDao();
	private IntSystemDao irDao = new IntSystemDao();
	private IRColumnsService IRColumnsService = ServiceLocator.get(IRColumnsService.class);
	private MyStore myStore = new MyStore();
	private UserOperService userOperService = ServiceLocator.get(UserOperService.class);
	
	
	Log log = LogFactory.getLog(this.getClass());
	
	@Override
	public boolean saveNewReport(IRReport irReport,Menu  menu , List<IRSearchConfig> ircList,
			List<IRColumns> colList,List<IRDrillConfig> irdList) throws Exception {
		
		//插入menu
		int menuId = 0;
		try{
			menuId = (Integer) menuService.save(menu);
			if(menuId == 0){
				return false;
			}
			menu.setAction("/intreport/create/"+menuId+"_1");
			
			if(!menuService.update(menu)){
				menuService.delete(Menu.class, menuId);
				log.info("update menu error!");
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			if(menuId > 0){
				menuService.delete(Menu.class, menuId);
			}
			return false;
		}
		
		//插入IRReport
		int reportId = 0;
		try{
			irReport.setMenuId(menuId);
			reportId =  (Integer) iRReportService.save(irReport);
			if(reportId == 0){
				menuService.delete(Menu.class, menuId);
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			menuService.delete(Menu.class, menuId);
			return false;
		}
		
		//批量插入查询配置及字段配置  (事务)  失败则删除之前插入的IRReport和Menu
		try{
			boolean result = irDao.saveNewReport(reportId, ircList, colList,irdList);
			if(!result){
				iRReportService.delete(IRReport.class, reportId);
				menuService.delete(Menu.class, menuId);
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			iRReportService.delete(IRReport.class, reportId);
			menuService.delete(Menu.class, menuId);
			return false;
		}
	
		return true;
	}

	
	@Override
	public boolean updateReportState(int reportId, int state) throws Exception {
		return irDao.updateReportState(reportId, state);
	}
	
	private String commonSql(int isCount,String tableName){
		StringBuffer rawSql = new StringBuffer();//原始SQL
		if(isCount == 1){
			rawSql.append("select count(1) from (");
		}
		rawSql.append("select *select* from "+tableName);
		rawSql.append(" where 1=1 *where*  ");
		rawSql.append(" *groupBy* ");
		rawSql.append(" *orderBy* ");
		if(isCount == 1){
			rawSql.append(" ) s");
		}
		return rawSql.toString();
	}
	
	
	@Override
	public  Paginate<String> IRsearchValues(IRReport  irReport,String condition, List<String> cols,List<String> drills,
			String orderBy, String groupby, int pageNum, int pageIndex,String specialValues,int dailysum)throws Exception {
		Paginate<String>  result = new Paginate<String>();
		
		if(irReport.getTableName().contains("*")){  //包含年月日通配
			//校验是否含有时间段查询条件
			List<IRSearchConfig>  sTypeList = iRSearchConfigService.getListByIRid(irReport.getId());
			boolean hasDateFrame = false;
			for(IRSearchConfig  irc : sTypeList){
				if(irc.getSearchTypeId() == 3){
					hasDateFrame = true;
					break;
				}
			}
			if(!hasDateFrame){
				return null;
			}
			/**判断查询的日期是否跨表 */
			//区分分类的类型  
			String subType = irReport.getTableName().substring(irReport.getTableName().indexOf("*")+1,irReport.getTableName().lastIndexOf("*"));
			List<String> searchTableList = new ArrayList<String>();//需查询的表集合
			String startDate = "";//查询开始日期
			String endDate = "";//查询结束日期
			for(String temp : specialValues.split(";")){
				String key = temp.split(":")[0];
				String value = temp.split(":")[1];
				if("startDate".equals(key)){
					startDate = value;
				}else if("endDate".equals(key)){
					endDate = value;
				}
			}
			searchTableList = DateUtil.subDateByPatter(startDate, endDate, subType);
			/**查询未跨表则直接更新表名，走普通查询 ；多表查询则走跨表查询*/
			if(searchTableList.size() > 1){
				result =  searchBySubMeter(irReport, condition, cols,drills, orderBy, groupby, pageNum, pageIndex,specialValues,searchTableList,dailysum);//分表查询，添加钻取字段
			}else if(searchTableList.size() == 1){
				irReport.setTableName(irReport.getTableName().replace("*"+subType+"*", searchTableList.get(0)));
				result =  searchByOneTable(irReport, condition, cols,drills, orderBy, groupby, pageNum, pageIndex,dailysum);//单表查询
			}else{
				return  null;
			}
		}else{
			result =  searchByOneTable(irReport, condition, cols,drills, orderBy, groupby, pageNum, pageIndex,dailysum);//单表查询
		}
		
		return result;
	}
	
	@Override
	public  HashMap<String,String>  IRsearchValuesNonPage(IRReport  irReport,String condition, List<String> cols,List<String> drills, String groupby,String specialValues) throws Exception{
		Paginate<String> pList=null;
		if(irReport.getTableName().contains("*")){  //包含年月日通配
			//校验是否含有时间段查询条件
			List<IRSearchConfig>  sTypeList = iRSearchConfigService.getListByIRid(irReport.getId());
			boolean hasDateFrame = false;
			for(IRSearchConfig  irc : sTypeList){
				if(irc.getSearchTypeId() == 3){
					hasDateFrame = true;
					break;
				}
			}
			if(!hasDateFrame){
				return null;
			}
			/**判断查询的日期是否跨表 */
			//区分分类的类型  
			String subType = irReport.getTableName().substring(irReport.getTableName().indexOf("*")+1,irReport.getTableName().lastIndexOf("*"));
			List<String> searchTableList = new ArrayList<String>();//需查询的表集合
			String startDate = "";//查询开始日期
			String endDate = "";//查询结束日期
			for(String temp : specialValues.split(";")){
				String key = temp.split(":")[0];
				String value = temp.split(":")[1];
				if("startDate".equals(key)){
					startDate = value;
				}else if("endDate".equals(key)){
					endDate = value;
				}
			}
			searchTableList = DateUtil.subDateByPatter(startDate, endDate, subType);
//			log.info("searchTableList:"+searchTableList);
			/**查询未跨表则直接更新表名，走普通查询 ；多表查询则走跨表查询*/
			if(searchTableList.size() > 1){
				pList=searchBySubMeter(irReport, condition, cols,drills, "", groupby, Integer.MAX_VALUE, 1,specialValues,searchTableList,1);//分表查询
			}else if(searchTableList.size() == 1){
				irReport.setTableName(irReport.getTableName().replace("*"+subType+"*", searchTableList.get(0)));
				pList=searchByOneTable(irReport, condition, cols,drills, "", groupby,  Integer.MAX_VALUE, 1,1);//单表查询
			}else{
				return null;
			}
		}else{
			pList= searchByOneTable(irReport, condition, cols,drills, "", groupby, Integer.MAX_VALUE, 1,1);//单表查询
		}
		
		//流量来源报表  来源字段需要通过对应的中英文字典翻译
		int menuId = irReport.getMenuId();
		if(!pList.getItems().isEmpty() && (menuId>=269 && menuId <= 272) && groupby.contains("source") ){
			pList = flowSourceNameTran(pList);
		}
		
		
		HashMap<String,String> keyValue=new HashMap<String,String>();
		List<String> items =pList.getItems(); // 获得结果集
//		log.info("第二次查询："+items.get(0));
		String gbs[] = groupby.split(",");
		for (String recode : items) { // 对每一条记录提取维度值，放入对应的集合中
			StringBuilder key=new StringBuilder();
			String cells[] = recode.split(";", gbs.length + 1);
			for (int i = 0; i < gbs.length; i++) { // 维度
				key.append(cells[i].split(",")[0]);
				if(i+1<gbs.length)
					key.append("_");
			}
			keyValue.put(key.toString(), cells[gbs.length]);
		}
		return keyValue;
	}
	
	/**单表查询
	 */
	private  Paginate<String> searchByOneTable(IRReport  irReport,String condition, List<String> cols,List<String> drills,
			String orderBy, String groupby, int pageNum, int pageIndex,int dailysum)throws Exception {
		String groupBy = "";
		if("groupBy".equals(cols.get(0))){  //通配查询：当查询第一项为分组通用项时
			if(!"".equals(groupby)){//替换查询项中第一项为分组维度字段
				groupBy = "group by "+groupby;
				List<String> newCols = new ArrayList<String>();
				for(int i = 0 ; i < groupby.split(",").length; i++){
					String gb = groupby.split(",")[i];
					newCols.add(gb);
				}
				cols.remove(0);
				newCols.addAll(cols);
				cols = newCols;
			}else if("".equals(groupby) ){  //无维度时，默认为总计维度
				cols.remove(0);
				cols.add(0, "'total'");
			}
		}else{  //查询无分组通用项时
			if(!"".equals(groupby)){
				groupBy = "group by "+groupby;
			}
		}
		
		//count总记录数Sql
		int count = 0;
		if("'total'".equals(cols.get(0))){
			count = 1;
		}else{
			String countSql = commonSql(1, irReport.getTableName());
			countSql = countSql.replace("*where*", condition);
			countSql = countSql.replace("*groupBy*", groupBy);
			countSql = countSql.replace("*orderBy*", "");
			countSql = countSql.replace("*select*", "1");
			log.info("count sql is : " + countSql);
			log.info(DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
			count = comDao.countAll(countSql, irReport.getDBconfig());
		}
		//查询Sql
		String searchSql = commonSql(0, irReport.getTableName());
		searchSql = searchSql.replace("*where*", condition);
		searchSql = searchSql.replace("*groupBy*", groupBy);
		searchSql = searchSql.replace("*orderBy*", orderBy);
		
		List<IRColumns>  irColList =  IRColumnsService.getColsByReportId(irReport.getId());
		List<Integer> colTypes = new ArrayList<Integer>();//查询字段的类型集合，用于查询时候拼接返回结果及确定RS下标
		HashMap<String,IRColumns>  irColMap = new HashMap<String,IRColumns>();  // key:字段英文名  便于检索
		
		for(IRColumns vo : irColList){
			if("groupBy".equals(vo.getEname())){ 
				continue;
			}
			irColMap.put(vo.getEname(), vo);
		}
		//groupby 单独处理 
		if(!"".equals(groupby)){ //根据groupby的分词个数  虚构对应的IRColumns  默认无特殊计算SQL  类型为字符
			for(int i = 0 ; i < groupby.split(",").length; i ++){
				String gb = groupby.split(",")[i];
				IRColumns vo = new IRColumns();
				vo.setColType(0);
				vo.setSpecialInSql("");
				irColMap.put(gb, vo);
			}
		}
		
		//拼查询项目SQL
		StringBuffer  select  = new StringBuffer();
		for(int i = 0 ; i < cols.size() ; i++){
			String col = cols.get(i);
			if(irColMap.containsKey(col)){
				IRColumns  columns = irColMap.get(col);
				if("".equals(columns.getSpecialInSql())){
					select.append(col);
				}else{//传入平均参数，若不为0，则除之,并且不为平均数
					if(dailysum!=1&&(columns.getColType()!=3)&&(!columns.getSpecialInSql().contains("/")||columns.getSpecialInSql().endsWith("/100")))
					{
					select.append("("+columns.getSpecialInSql()+")/"+dailysum+" as "+col);	
					}else{
					select.append(columns.getSpecialInSql()+" as "+col);
					}
				}
				
				if((i+1) < cols.size()){
					select.append(",");
				}
				colTypes.add(columns.getColType()); //查询字段类别
				
				
			}else if("'total'".equals(col)){//总计维度查询
				select.append(col+",");
				colTypes.add(0);
			}
			
		}
		//钻取条件添加
		HashMap<String, HashMap<String, Integer>> drillMap=new HashMap<String, HashMap<String,Integer>>();
		if(drills!=null&&drills.size()>0)
		 {
			for(int i = 0 ; i < groupby.split(",").length; i ++){
				String gb = groupby.split(",")[i];
				if(drills.contains(gb))
				{
					HashMap<String, Integer> tempdrillHashMap=new HashMap<String, Integer>();
					for(int j=0;j<groupby.split(",").length;j++)
					{
						String tempgb=groupby.split(",")[j];
						tempdrillHashMap.put(tempgb, cols.indexOf(tempgb));
					}	
					drillMap.put(gb, tempdrillHashMap);
						
				}
			}
		 }
		
		searchSql = searchSql.replace("*select*", select.toString());
		if(pageIndex >= 0){
			String limit ="limit "+(pageIndex-1)*pageNum+","+pageNum;//limit分页
			searchSql = searchSql+ " "+limit;
		}
		log.info("searchSql: "+searchSql);
		log.info(DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
		List<String>  rsList = irDao.irSearchData(searchSql.toString(), colTypes, irReport.getDBconfig(),drillMap);
		Paginate<String> pList = new Paginate<String>(count,pageIndex,pageNum,rsList);
		return pList;
	}
	
	
	/**数据库分表查询
	 */
	private  Paginate<String> searchBySubMeter(IRReport  irReport,String condition, List<String> cols,List<String> drills,
			String orderBy, String groupby, int pageNum, int pageIndex,String specialValues,List<String> searchTableList,int dailysum)throws Exception {
		/**step 1  获取特殊字段中的内容 */
		String startDate = "";//查询开始日期
		String endDate = "";//查询结束日期
		String dateName = "";  //日期绑定字段
		for(String temp : specialValues.split(";")){
			String key = temp.split(":")[0];
			String value = temp.split(":")[1];
			if("startDate".equals(key)){
				startDate = value;
			}else if("endDate".equals(key)){
				endDate = value;
			}else if("dateName".equals(key)){
				dateName = value;
			}
		}
		
		/**step 2 确认各表的查询条件 */
		String subType = irReport.getTableName().substring(irReport.getTableName().indexOf("*")+1,irReport.getTableName().lastIndexOf("*"));//分表的类型  
		String table = irReport.getTableName().substring(0,irReport.getTableName().indexOf("*"));//原始表明（不带分页时间段）
		Map<String,String > searchConditionMap = new HashMap<String,String>();//各表查询条件
		String oldBeginDate = " and "+dateName+">='"+startDate+"' ";//原查询条件中的开始日期段
		String oldEndDate = " and "+dateName+"<='"+endDate+"' ";//原查询条件中的结束日期段
		String searchBeginDate = "";
		String searchEndDate = "";
		
		List<String> tempSearchTableList = new ArrayList<String>();
		
		for(String temp : searchTableList){
			String newCon = "";
			//获得各表查询条件
			if("yyyyMM".equals(subType)){
				String month = temp;
				if(DateUtil.getStringDate(DateUtil.strToDate(startDate, "yyyy-MM-dd"), "yyyyMM").equals(month)){
					searchBeginDate = startDate;
				}else{
					searchBeginDate = month.substring(0,4)+"-"+month.substring(4)+"-01";
				}
				if(DateUtil.getStringDate(DateUtil.strToDate(endDate, "yyyy-MM-dd"), "yyyyMM").equals(month)){
					searchEndDate = DateUtil.getStringDate(DateUtil.getDateAfter(DateUtil.strToDate(endDate, "yyyy-MM-dd"),1), "yyyy-MM-dd");
				}else{
					searchEndDate = DateUtil.getStringDate(DateUtil.getDateAfterMonth(DateUtil.strToDate(month.substring(0,4)+"-"+month.substring(4)+"-01", "yyyy-MM-dd"), 1), "yyyy-MM-dd");
				}
				String newBeginDate = " and "+dateName+">='"+searchBeginDate+"' ";
				String newEndDate = " and "+dateName+"<'"+searchEndDate+"' ";
				newCon = condition.replace(oldBeginDate, newBeginDate);
				newCon = condition.replace(oldEndDate, newEndDate);
			}else if("yyyy".equals(subType)){
				String year = temp;
				if(DateUtil.getStringDate(DateUtil.strToDate(startDate, "yyyy-MM-dd"), "yyyy").equals(year)){
					searchBeginDate = startDate;
				}else{
					searchBeginDate = year+"-01-01";
				}
				if(DateUtil.getStringDate(DateUtil.strToDate(endDate, "yyyy-MM-dd"), "yyyy").equals(year)){
					searchEndDate = endDate;
				}else{
					searchEndDate = year+"-12-31";
				}
				String newBeginDate = " and "+dateName+">='"+searchBeginDate+"' ";
				String newEndDate = " and "+dateName+"<='"+searchEndDate+"' ";
				newCon = condition.replace(oldBeginDate, newBeginDate);
				newCon = condition.replace(oldEndDate, newEndDate);
			}else if("yyyyMMdd".equals(subType))
			{
				String nowdate=temp;
				String newBeginDate = " and "+dateName+">='"+nowdate+"' ";
				String newEndDate = " and "+dateName+"<='"+nowdate+"' ";
				newCon = condition.replace(oldBeginDate, newBeginDate);
				newCon = condition.replace(oldEndDate, newEndDate);
			}
			//重拼完整表名,与查询条件放入MAP
			temp  = table+temp;
			tempSearchTableList.add(temp);
			searchConditionMap.put(temp, newCon);
		}
		searchTableList = tempSearchTableList;
		
		
		/**step 3 各月总记录数查询及总量汇总  */
		String groupBy = "";
		if("groupBy".equals(cols.get(0))){  //通配查询：当查询第一项为分组通用项时
			if(!"".equals(groupby)){//替换查询项中第一项为分组维度字段
				groupBy = "group by "+groupby;
				List<String> newCols = new ArrayList<String>();
				for(int i = 0 ; i < groupby.split(",").length; i ++){
					String gb = groupby.split(",")[i];
					newCols.add(gb);
				}
				cols.remove(0);
				newCols.addAll(cols);
				cols = newCols;
			}else if("".equals(groupby) ){  //无维度时，默认为总计维度
				cols.remove(0);
				cols.add(0, "'total'");
			}
		}else{  //查询无分组通用项时
			if(!"".equals(groupby)){
				groupBy = "group by "+groupby;
			}
		}
		
		int allCount = 0; //所有数据量
		Map<String,Integer> searchCountMap = new HashMap<String,Integer>();//各表总数据量
		List<String> searchCountList = new ArrayList<String>();//按照各表记录数划分的区间，以便后续查询定位limit查询区间范围
		if("'total'".equals(cols.get(0))){ //总计维度下 总记录数就1条
			allCount = 1;
		}else{
			for(String tableName : searchTableList){
				//总计Sql
				String countSql = commonSql(1, tableName);
				countSql = countSql.replace("*where*", searchConditionMap.get(tableName));
				countSql = countSql.replace("*groupBy*", groupBy);
				countSql = countSql.replace("*orderBy*", "");
				countSql = countSql.replace("*select*", "1");
				log.info(" sub table count sql : " + countSql);
				log.info(DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss"));
				int count = comDao.countAll(countSql, irReport.getDBconfig());
				searchCountMap.put(tableName, count);
				searchCountList.add(tableName+"_"+(allCount+1)); 
				allCount = allCount + count;
			}
		}
		
		//钻取条件添加
		HashMap<String, HashMap<String, Integer>> drillMap=new HashMap<String, HashMap<String,Integer>>();
		if(drills!=null&&drills.size()>0)
		 {
			for(int i = 0 ; i < groupby.split(",").length; i ++){
				String gb = groupby.split(",")[i];
				if(drills.contains(gb))
				{
					HashMap<String, Integer> tempdrillHashMap=new HashMap<String, Integer>();
					for(int j=0;j<groupby.split(",").length;j++)
					{
						String tempgb=groupby.split(",")[j];
						tempdrillHashMap.put(tempgb, cols.indexOf(tempgb));
					}	
					drillMap.put(gb, tempdrillHashMap);
						
				}
			}
		 }
		/**step 4 查询Sql拼写*/
		String searchSql = commonSql(0, "*tableName*");
		searchSql = searchSql.replace("*groupBy*", groupBy);
		searchSql = searchSql.replace("*orderBy*", orderBy);
		
		List<IRColumns>  irColList =  IRColumnsService.getColsByReportId(irReport.getId());
		List<Integer> colTypes = new ArrayList<Integer>();//查询字段的类型集合，用于查询时候拼接返回结果及确定RS下标
		HashMap<String,IRColumns>  irColMap = new HashMap<String,IRColumns>();  // key:字段英文名  便于检索
		for(IRColumns vo : irColList){
			if("groupBy".equals(vo.getEname())){ 
				continue;
			}
			irColMap.put(vo.getEname(), vo);
		}
		//groupby 单独处理 
		if(!"".equals(groupby)){ //根据groupby的分词个数  虚构对应的IRColumns  默认无特殊计算SQL  类型为字符
			for(int i = 0 ; i < groupby.split(",").length; i ++){
				String gb = groupby.split(",")[i];
				IRColumns vo = new IRColumns();
				vo.setColType(0);
				vo.setSpecialInSql("");
				irColMap.put(gb, vo);
			}
		}
		
		StringBuffer  select  = new StringBuffer(); //查询项
		for(int i = 0 ; i < cols.size() ; i++){
			String col = cols.get(i);
			if(irColMap.containsKey(col)){
				IRColumns  columns = irColMap.get(col);
				if("".equals(columns.getSpecialInSql())){
					select.append(col);
				}else{
					select.append(columns.getSpecialInSql()+" as "+col);
				}
				
				if((i+1) < cols.size()){
					select.append(",");
				}
				colTypes.add(columns.getColType()); //查询字段类别
			}else if("'total'".equals(col)){//总计维度查询
				select.append(col+",");
				colTypes.add(0);
			}
			
		}
		searchSql = searchSql.replace("*select*", select.toString());
		
		
		/**step 5 数据查询   根据查询维度是否包含日期维度  进行两种不同的处理  */
		List<String>  finalRsList  = new ArrayList<String>(); //最终结果集
		
		/**查询维度不包含日期的处理
		 *   1.查询各月数据 全部放在一起
		 *   2.特殊计算公式指标依赖检查及查询处理
		 *   3.根据维度进行数据重新聚合 （*重要逻辑*  具体参考sumDataForIR注释 ）
		 *   4.排序
		 *   5.如需分页则做limit分割处理
		 *   
		 */
		if("'total'".equals(cols.get(0)) || (!groupby.contains(dateName) && !"".equals(groupby))){//不包含日期处理
			//1.查询各月数据并全部放在一起 
			List<String>  tempList = new ArrayList<String>();
			
			
			/** fix bug -2015.7.5 : 跨月非日期查询  只查询某特殊计算公式指标，而没勾选公式中依赖的指标，导致无数据 
			 * 	 逻辑如下:
			 *   2-1.分析所有指标的计算公式，聚合所有依赖字段;
			 *   2-2.检查是否所有依赖字段都在查询中;
			 *   2-3.如没查询，则加入临时查询;
			 *   2-4.待数据重组完毕后再去掉冗余的数据结果。
			 * 
			 */
			
			//2-1  分析所有指标的计算公式，聚合所有依赖字段;
			Set<String> allSearchColsSet = new HashSet<String>();
			for(String searchCol : cols){
				String formula = irColMap.get(searchCol).getSpecialInSql();
				Set<String> colsSet = MathEval.checkDepCols(formula);
				if(!colsSet.isEmpty()){
					allSearchColsSet.addAll(colsSet);
		
				}
			}
			
			//2-2检查是否所有依赖字段都在查询字段中 ， 如果有不在查询字段中的，则拼出select ，准备加入查询语句
			List<String> addCols = new ArrayList<String>();
			for (String col : allSearchColsSet) {
				if(!cols.contains(col)){
					addCols.add(col);
				}
			}
			
			StringBuffer addSelect  = new StringBuffer();
			
			if(!addCols.isEmpty()){
				cols.addAll(addCols);
				
				for(int i = 0 ; i < addCols.size() ; i++){
					String col = addCols.get(i);
					if(irColMap.containsKey(col)){
						IRColumns  columns = irColMap.get(col);
						if("".equals(columns.getSpecialInSql())){
							addSelect.append(col);
						}else{
							addSelect.append(columns.getSpecialInSql()+" as "+col);
						}
						
						if((i+1) < addCols.size()){
							addSelect.append(",");
						}
						colTypes.add(columns.getColType());
					}
				}
			}
			
			
			
			//查询所有数据
			for(String tableName : searchTableList){
				String sql  = searchSql.replace("*where*", searchConditionMap.get(tableName)).toLowerCase();//替换后的查询SQL
				sql = sql.replace("*tablename*", tableName);
				//2-3
				if(!StringUtil.isEmpty(addSelect.toString())){
					sql = sql.replace("from", "  ,  " + addSelect.toString() + " from ");
				}
				List<String>  rsList = irDao.irSearchData(sql.toString(), colTypes, irReport.getDBconfig(),drillMap);
				tempList.addAll(rsList);
			}
			
			if(!tempList.isEmpty()){
				//只有一种情况不用重组数据  即 报表为明细类报表（无groupBy选择）
				if(!"".equals(groupby) || "'total'".equals(cols.get(0))){
					//2.数据重组
					tempList = sumDataForIR(tempList, cols, irColMap, groupBy,addCols.size(),dailysum);
				}
				
				
				//总记录数
				allCount = tempList.size(); 
				
				//3.重新排序
				if(!"".equals(orderBy)){
					tempList = SortUtil.sortIRData(tempList, orderBy, cols);
				}
				
				
				//2-4 去掉冗余的依赖计算数据结果
				if(!addCols.isEmpty()){
					List<String> newValueList = new ArrayList<String>();
					int nowColsSize = cols.size();
					int addColsSize = addCols.size();
					int showColsSize = nowColsSize - addColsSize;
					
					for(String values : tempList){
						String temps[]  = values.split(";");
						StringBuffer newValue = new StringBuffer();
						for(int i = 0 ; i < showColsSize ; i ++ ){
							newValue.append(temps[i]);
							newValue.append(";");
						}
						//System.out.println(newValue.toString());
						newValueList.add(newValue.toString());
					}
					tempList = newValueList;
				}
				
				//4.如果不是导出Excel  则需要 limit分页
				if(tempList.size() > pageNum &&  pageIndex >= 0){
					int begin  = (pageIndex-1)*pageNum;
					List<String> limitList = new ArrayList<String>();
					for(int i = begin ; i < (begin+pageNum); i++){
						limitList.add(tempList.get(i));
						if((i+1) == tempList.size()){
							break;
						}
					}
					tempList = limitList;
				}
			}
			finalRsList.addAll(tempList);
		}else{ 
			/**  查询维度不包含日期的处理逻辑
			 * 	  分为两种
			 *   1.排序为date
			 *   	判断是否为EXCEL导出 -> 是-则全部查询数据 ;  否-则进行偏移量计算，limit够了则输出。
			 *   2.排序不为date：
			 *   	查询全部数据； 排序;  如需limit分割则处理。
			 * */
			
			
			/**排序为date的处理：
			 * 判断是否为EXCEL导出 -> 是-则全部查询数据 ;  否-则进行偏移量计算，limit够了则输出。
			 */
			if(orderBy.contains(dateName)){
				//EXCEL导出
				if(pageIndex < 0){
					List<String>  tempList = new ArrayList<String>();
					for(String tableName : searchTableList){
						String sql  = searchSql.replace("*where*", searchConditionMap.get(tableName));//替换后的查询SQL
						sql = sql.replace("*tableName*", tableName);
						List<String>  rsList = irDao.irSearchData(sql.toString(), colTypes, irReport.getDBconfig(),null);
						tempList.addAll(rsList);
					}
					finalRsList = tempList;
				}else{//limit切割
					//分页查询数据区间  起始点 结束点
					int beginNum = (pageIndex-1)*pageNum+1; 
					int hasCollectCount = 0;
					//选定起始区间
					boolean  hasSelect = false;
					for(int i = 0 ; i<searchCountList.size();i++){
						//当已收录数据数满足当页数据记录数时，查询结束 
						if(hasCollectCount >= pageNum){
							break;
						}
						String tableName = searchCountList.get(i).substring(0,searchCountList.get(i).lastIndexOf("_")); 
						String limit = "";
						if(!hasSelect){
							int begin =Integer.valueOf(searchCountList.get(i).substring(searchCountList.get(i).lastIndexOf("_")+1));
							int end = 0;
							if((i+1) == searchCountList.size()){
								end = 999999;
							}else{
								end =Integer.valueOf(searchCountList.get(i+1).substring(searchCountList.get(i+1).lastIndexOf("_")+1));
							}
							if(begin<=beginNum && beginNum<end){
								hasSelect = true;
								int limitBegin = beginNum  - begin ; //查询limit区间的起始
								limit ="limit "+limitBegin+","+pageNum;//limit分页
							}
						}else{
							limit ="limit "+0+",";//limit分页
							int searchCount = searchCountMap.get(tableName);
							if(searchCount >= (pageNum - hasCollectCount)){
								limit = limit +(pageNum - hasCollectCount);
							}else{
								limit = limit+pageNum;
							}
						}
						if("".equals( limit)){
							continue;
						}
						String sql  = searchSql.replace("*where*", searchConditionMap.get(tableName));//替换后的查询SQL
						sql = sql.replace("*tableName*", tableName);
						sql = sql+ " "+limit;
						
						List<String>  rsList = irDao.irSearchData(sql.toString(), colTypes, irReport.getDBconfig(),drillMap);
						if(!rsList.isEmpty()){
							hasCollectCount = hasCollectCount + rsList.size();
							finalRsList.addAll(rsList);
						}
					}
				}
			}else{
				/**排序不为date的处理
				 *  1.查询全部数据；  2.排序;  3.如需limit分割则处理。
				 */
				
				//1.查询全部数据
				List<String>  tempList = new ArrayList<String>();
				for(String tableName : searchTableList){
					String sql  = searchSql.replace("*where*", searchConditionMap.get(tableName));//替换后的查询SQL
					sql = sql.replace("*tableName*", tableName);
					List<String>  rsList = irDao.irSearchData(sql.toString(), colTypes, irReport.getDBconfig(),drillMap);
					tempList.addAll(rsList);
				}
				
				
				//2.排序
				if(!"".equals(orderBy)){
					tempList = SortUtil.sortIRData(tempList, orderBy, cols);
				}
				
				//3.如果不是导出Excel  则需要 limit分页
				if(tempList.size() > pageNum &&  pageIndex >= 0){
					int begin  = (pageIndex-1)*pageNum;
					List<String> limitList = new ArrayList<String>();
					for(int i = begin ; i < (begin+pageNum); i++){
						limitList.add(tempList.get(i));
						if((i+1) == tempList.size()){
							break;
						}
					}
					tempList = limitList;
				}
				finalRsList = tempList;
			}
		}
		
		Paginate<String> pList = new Paginate<String>(allCount,pageIndex,pageNum,finalRsList);
		return pList;
	}


	@Override
	public boolean updateEditedReport(IRReport irReport, Menu menu,
			List<IRSearchConfig> ircList, List<IRColumns> colList,List<IRDrillConfig> irdList)
			throws Exception {
		//更新menu
		Menu oldMenu = menuService.findById(Menu.class, menu.getId()); //之前的旧数据
		try{
			if(!menuService.update(menu)){
				log.info("update menu error!");
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
		
		//更新IRReport 失败则恢复原 MENU数据  
		IRReport oldIrReport = iRReportService.getById(irReport.getId());
		try{
			if(!iRReportService.update(irReport)){
				log.info("update irReport error!");
				menuService.update(oldMenu);
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			menuService.update(oldMenu);
			return false;
		}
		
		
		/**  处理编辑过后的查询配置以及显示字段配置
		 *  1.查出原数据  backup
		 *  2.批量插入新数据  删除原数据  （事务处理）
		 *  3.失败则将MENU和IRREPORT数据回滚
		 *     */
		try{
			boolean result = irDao.updateEditedReport(irReport.getId(),ircList, colList,irdList);
			if(!result){
				iRReportService.update(oldIrReport);
				menuService.update(oldMenu);
				return false;
			}
		}catch (Exception e) {
			e.printStackTrace();
			iRReportService.update(oldIrReport);
			menuService.update(oldMenu);
			return false;
		}
		return true;
	}


	@SuppressWarnings("static-access")
	@Override
	public void inserVisitRecord(String userName, String url) throws Exception {
		
		String sql = "insert into visit_record (id,visitTime,userName,url,funName,funCategory) values ('%s','%s','%s','%s','%s','%s')";
		Date date = new Date();
		long id = date.getTime();
		String visitTime = DateUtil.getStringDate(date, "yyyy-MM-dd HH:mm:ss");
		
		String urlKey = url.substring(url.lastIndexOf("/")+1);
		if(urlKey.contains("_")){
			urlKey = urlKey.substring(0,urlKey.indexOf("_"));
		}
		
		
		String funName = "";
		String funCategory = "";
		if(myStore.visitMenuMap.isEmpty() || !myStore.visitMenuMap.containsKey(urlKey)){
			myStore.visitMenuMap.clear();
			myStore.getAllMenuMap();
		}
		Menu menu = myStore.visitMenuMap.get(urlKey);
		funName = menu.getName();
		funCategory = menu.getCate1();
		comDao.executeUpdate(String.format(sql, id,visitTime,userName,url,funName,funCategory), DBConfig.db_68_bi_sys.getIntType());
	}
	
	

	/**自定义报表  跨月数据重计算    对sql进行钻取条件更新
	 * 
	 */
	private List<String>  sumDataForIR(List<String> tempList , List<String> cols,HashMap<String,IRColumns>  irColMap,String groupby,int addcolssize,int dailysum){
		/** 
		 * 根据查询项，按照每项的计算公式对数据进行总计计算
		 *  详细：根据查询指标每项的指标属性，分别处理：
		 *  1.总计数据直接写入；
		 *  2.字符串类型从第一项中取值 ；
		 *  3.数字类型数据 -  含运算符的计算公式 最后处理   /  不含的直接sum
		 *  4.处理特殊计算公式指标  - 将已算好的正常数字类型数据替代进计算公式 并用SQL执行结果 
		 *  
		 *  Note:
		 *  1.  特殊计算指标先后顺序规则： 按照下标先后顺序进行，如有某个特殊计算指标依赖的指标也是特殊类型，则需要依赖的指标排在其前面
		 *       如: a = sum(A)/sum(B)     b = sum(a)/100   则需要先计算a才能代入到b的公式  所以在配置报表时需注意此点
		 *  2. 指标计算规则非常复杂，除了sum还有别的计算规则的报表   暂时不允许跨月  
		 */
		List<Integer>  spCols = new ArrayList<Integer>();//特殊计算指标下标集
		List<Integer>  norCols = new ArrayList<Integer>();//正常处理的指标下标集
		List<Integer>  keyCols = new ArrayList<Integer>();//key(groupby维度) 指标下标集
		
		Map<String,Integer> colMap = new HashMap<String, Integer>();//字段名与下标MAP    后面为特殊计算指标公式替换用
		
		//查询维度集合  ep:  group by disp_city1_name,business_type_biz,disp_cate1_name
		String[] gbArrays = groupby.toLowerCase().replace("group by", "").trim().split(",");
		List<String> gbList = Arrays.asList(gbArrays);
		
		//将查询项的cols按照字符长度倒序重排  用于后面的特殊计算公式指标替代  避免出现局部匹配替代
		List<String>  sortCols = new ArrayList<String>();
		sortCols = SortUtil.sortListByLengthDesc(cols);
		
		//指标分类
		for(int  i = 0; i< cols.size(); i++){
			String col = cols.get(i);
			if("'total'".equals(col) || gbList.contains(col.toLowerCase())){
				keyCols.add(i);
			}else{
				IRColumns column = irColMap.get(col);
				if(MathEval.checkHashEval(column.getSpecialInSql().replace("/100", ""))){ //检查查询SQL是否包含运算符 + - * /   （去掉/100后）
					spCols.add(i);
				}else{
					norCols.add(i);
				}
			}
			colMap.put(col, i);
		}
		
		//根据GROUPBY维度 组合KEY，将同维度数据放在一起
		Map<String,List<String>>  tempMap = new HashMap<String, List<String>>();
		for(String temp : tempList){
			String[] tempArrys = temp.split(";");
			StringBuffer  key  = new StringBuffer();
			for(int index : keyCols){
				String nodrillkey=tempArrys[index];
				//如果有钻取条件，去掉钻取条件参数
				if(tempArrys[index].contains(",key:"))
				{
					 nodrillkey=tempArrys[index].substring(0,tempArrys[index].indexOf(",key:"));
				
				}
				key.append(nodrillkey+";");
			}
			
			List<String>  dataList = new ArrayList<String>();
			if(tempMap.containsKey(key.toString())){
				dataList = tempMap.get(key.toString());
				dataList.add(temp);
			}else{
				dataList.add(temp);
				tempMap.put(key.toString(), dataList);
			}
		}
		
		List<String>  finalList = new ArrayList<String>();//最终结果集
		//计算数据
		for(Iterator<String>  it = tempMap.keySet().iterator(); it.hasNext();){
			try {
				String key = it.next();
				List<String> dataList = tempMap.get(key);
				String[] dataOneArray = dataList.get(0).split(";"); //将第一列数据取出  备用  取key类型和字符串数据用
				
				StringBuffer tempValues = new StringBuffer();
				
				TreeMap<String,Object>  colValueMap = new TreeMap<String, Object>(); //指标名与值对应MAP  后面处理特殊计算规则指标时 替换公式用
				for(int i = 0 ; i < cols.size() ; i++){
					int colType = Integer.parseInt(dataOneArray[i].split(",")[1]); //字段数据类型    0:字符   1:整形   2:浮点  3：百分比
					if(keyCols.contains(i)){  //维度指标
						/**
						 * 钻取条件处理，添加表名集合和日期集合,钻取条件只可能存在维度中,不用考虑日期，只用存储表名就行，添加一个tablelist
						 * */
						String dataoneresult=dataOneArray[i];
						if(dataOneArray[i].split(",").length>3&&!dataOneArray[i].split(",")[3].trim().equals("no")&&!dataOneArray[i].contains("#tablelist:"))                       //如果该列可钻取
						{
							dataoneresult=dataList.get(dataList.size()-1).split(";")[i];//取日期最长的
							String tablelist="#tablelist:";//一个表对应一个日期,注意定义区域
							for(String data : dataList){
								String[] dataArray = data.split(";");
								String tablename=dataArray[i].substring(dataArray[i].indexOf("from "), dataArray[i].indexOf(" where"));
								tablelist=tablelist+"@"+tablename;
							}
							dataoneresult=dataoneresult+tablelist+"#addcolssize:"+addcolssize;
						}
						tempValues.append(dataoneresult+";");
					}else if(spCols.contains(i)){ //计算公式数据待依赖的数据重算完毕后再算 先占位
						tempValues.append("todo,"+colType+";");
					}else{
						if(0 == colType){	//字符则直接取默认第一个
							tempValues.append(dataOneArray[i]+";");
						}else{ // 其他类型数据直接sum
							double sumValue = 0;
							for(String data : dataList){
								String[] dataArray = data.split(";");
								sumValue = sumValue + Double.valueOf(dataArray[i].split(",")[0]);
							}
							//如果有日均在此处理
							if(dailysum>1)
							{
								sumValue=sumValue/dailysum;
							}
							
							//不以科学计数
							NumberFormat nf = NumberFormat.getInstance();   
							nf.setGroupingUsed(false);  
							String tranSumValue = nf.format(sumValue);
							
							tempValues.append(tranSumValue+","+ colType +","+ tranSumValue +";");
							colValueMap.put(cols.get(i), tranSumValue);
						}
					}
				}
				
				
				
//				asList 会在下面的remove出错 UnsupportedOperationException
//				List<String> tempValueList = new ArrayList<String>();
//				String[] toCountSpeValuesArry = tempValues.toString().split(";");
//				tempValueList = Arrays.asList(toCountSpeValuesArry);
				
				List<String> tempValueList = new ArrayList<String>();
				for(String value : tempValues.toString().split(";")){
					tempValueList.add(value);
				}
				
				
				DecimalFormat df = new DecimalFormat("####.##");
				
				//特殊计算公式数据处理
				for(int index : spCols){
					String colName = cols.get(index); //指标名
					IRColumns column = irColMap.get(colName);
					String formula = column.getSpecialInSql().toLowerCase(); //计算公式
					
					for(String col : sortCols){
						if(formula.contains(col.toLowerCase())){
							Object value = colValueMap.get(col);
							formula = formula.replace(col.toLowerCase(), df.format(Double.parseDouble(value.toString())));
						}
					}
					
					Object tempRS = comDao.selectObject("select " + formula, DBConfig.db_68_bi_sys.getIntType()); //用SQL查询进行运算
					String rs = "0";
					if(null != tempRS){
						rs = tempRS.toString();
					}
					int colType  = Integer.parseInt(tempValueList.get(index).split(",")[1]);
					tempValueList.remove(index);
					tempValueList.add(index, rs+"," + colType + "," + rs);
					colValueMap.put(colName, rs);
					
				}
				
				//最终结果
				StringBuffer finalRS = new StringBuffer();
				for(int i = 0 ; i < tempValueList.size() ; i++){
					String r = tempValueList.get(i);
					finalRS.append(r);
					if((i+1) < tempValueList.size()){
						finalRS.append(";");
					}
				}
				finalList.add(finalRS.toString());
			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}
			
		}
		
		return finalList;
	}
	

	
	/**业务线细粒度权限检查
	 */
	public String businessOperCheck(User user,String showBusi)throws Exception{
		//用户操作权限 - 业务线判定
		if(!MyStore.admin.equals(user.getFunction())){ //管理员权限
			String operShowBusi = ""; //操作权限展示的业务线
			HashSet<Long> userOpersSet = userOperService.getUserOpers(user.getId());//用户全部细粒度权限
			List<OperMenu> operList = userOperService.getOperListByCate("全局控制", "业务线权限"); //该系列所有正常数据权限
			for (OperMenu operMenu : operList) { //如果用户细粒度权限中没有此类权限约束  即不做控制处理
				long operId = operMenu.getId();
				if(userOpersSet.contains(operId)){
					if(operId == 12){ //全部业务线权限
						break;
					}else{
						operShowBusi = operShowBusi +operMenu.getName()+",";
					}
				}
			}
			
			if(operShowBusi.length()>0){
				operShowBusi = operShowBusi.substring(0,operShowBusi.lastIndexOf(","));
				/** 操作权限展示的业务线 与 报表配置展现比较
				*    1.showBusi为空则按operShowBusi展示
				*    2.都不为空则取两者交集  无交集则说明该报表权限根本不该开给该用户......
				*/
				if("".equals(showBusi) ){
					showBusi = operShowBusi;
				}else{
					String temp = "";
					for(String showTemp : showBusi.split(",")){
						if(operShowBusi.contains(showTemp)){
							temp = temp + showTemp+",";
						}
					}
					if(temp.length() == 0 ){
						return "error";
					}else{
						showBusi = temp.substring(0,temp.lastIndexOf(","));
					}
				}
			}
		}
		
		return showBusi;
	}
	
	public Paginate<String> flowSourceNameTran(Paginate<String>  result){
		List<String> tempList = result.getItems();
		List<String> newRsList = new ArrayList<String>(); 
		for (String temps : tempList) {
			StringBuffer newValuesBuffer = new StringBuffer();
			for (String temp : temps.split(";")) {
				String value = temp.split(",")[0];
				String initialVal = value;
				String type = temp.split(",")[1];
				String drillcols="";
				if("0".equals(type) && MyStore.flowSourceDicMap.containsKey(value)){
					value = MyStore.flowSourceDicMap.get(value);
				}
				if(temp.split(",").length>3&&temp.split(",")[3]!=null)
				{
					drillcols=","+temp.substring(temp.indexOf(temp.split(",")[3]),temp.length());
				}
				
				newValuesBuffer.append(value +"," + type + "," + initialVal+drillcols+ ";");
			}
			newRsList.add(newValuesBuffer.toString());
		}
		result.setItems(newRsList);
		
		return result;
	}


	@SuppressWarnings("static-access")
	@Override
	public void inserSearchRecord(String menuId, String groupBy ,String userName,String searchCols) throws Exception {
		String sql = "insert into search_record (searchTime,userName,menuName,menuCate1,menuCate2,groupBy,searchCols) values ('%s','%s','%s','%s','%s','%s','%s')";
		
		String searchTime = DateUtil.getStringDate(new Date(), "yyyy-MM-dd HH:mm:ss");
		
		if(myStore.visitMenuMap.isEmpty() || !myStore.visitMenuMap.containsKey(menuId)){
			myStore.visitMenuMap.clear();
			myStore.getAllMenuMap();
		}
		Menu menu = myStore.visitMenuMap.get(menuId);
		
		
		comDao.executeSql(String.format(sql, searchTime,userName,menu.getName(),menu.getCate1(),menu.getCate2(),groupBy,searchCols), DBConfig.db_68_bi_sys.getIntType());
		
		
		
	}


	@Override
	public String cityOperCheck(User user) throws Exception {
		String operCitys = ""; 
		
		if(MyStore.admin.equals(user.getFunction())){ //管理员权限
			operCitys = MyStore.cityOperAll;
			return operCitys;
		}
		
		 
		HashSet<Long> userOpersSet = userOperService.getUserOpers(user.getId());//用户全部细粒度权限
		List<OperMenu> operList = userOperService.getOperListByCate("全局控制", "城市权限"); //该系列所有正常数据权限
		
		if(operList.isEmpty()){ //如果用户细粒度权限中没有此类权限约束  即不做控制处理
			operCitys = MyStore.cityOperAll;
			return operCitys;
		}else{
			for (OperMenu operMenu : operList) { 
				long operId = operMenu.getId();
				if(userOpersSet.contains(operId)){
					if(operId == 21){ //直营城市权限
						operCitys = MyStore.cityOperZY;
						break;
					}else if(operId == 22){ //渠道城市权限
						operCitys = MyStore.cityOperQD;
						break;
					}else{
						operCitys = operCitys + "'" + operMenu.getName()+"',";
					}
				}
			}
			
		}
		
		if("".equals(operCitys)){//该用户没有城市细粒度权限  则不用限制
			operCitys = MyStore.cityOperAll;
		}
		
		return operCitys;
	}
}
